|
SQL Server 2017 : Windows Authentication
2017/10/11 |
|
Configure SQL Server on Linux to enable Windows Authentication.
|
|||||||||
| [1] |
Add CentOS Host which SQL Server runs to Active Directory Domain, refer to here.
This example is based on the environment like follows.
|
||||||||
| [2] | Create a domain user on AD for SQL Server service. On this example, create [mssql] user like follows. |
|
| [3] |
Run Powershell with admin priviledge and set service principal to SQL Server service user.
PS > setspn -A MSSQLSvc/(SQL Server Hosts' FQDN):(SQL Server Port) (SQL Server service user)
|
|
| [4] | Configure on CentOS Host which SQL Server runs. |
|
# get Kerberos ticket with SQL Server service user [root@dlp ~]# kinit mssql@SRV.WORLD Password for mssql@SRV.WORLD: # make sure Key Version Number (kvno) [root@dlp ~]# kvno MSSQLSvc/dlp.srv.world:1433 MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: kvno = 2 # create keytab file # addent -password -p MSSQLSvc/(SQL Server Host's FQDN):(SQL Server Port) -k (kvno) [root@dlp ~]# ktutil ktutil: addent -password -p MSSQLSvc/dlp.srv.world:1433@SRV.WORLD -k 2 -e aes256-cts-hmac-sha1-96 Password for MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: ktutil: addent -password -p MSSQLSvc/dlp.srv.world:1433@SRV.WORLD -k 2 -e rc4-hmac Password for MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: ktutil: wkt /var/opt/mssql/secrets/mssql.keytab ktutil: quit
[root@dlp ~]#
chown mssql. /var/opt/mssql/secrets/mssql.keytab [root@dlp ~]# chmod 400 /var/opt/mssql/secrets/mssql.keytab
# set keytab file [root@dlp ~]# /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@dlp ~]# systemctl restart mssql-server |
| [5] | Create SQL Server Login. |
|
# for example, create [serverworld] user's login [root@dlp ~]# id serverworld uid=1539401000(serverworld) gid=1539400513(domain users) groups=1539400513(domain users) [root@dlp ~]# sqlcmd -S localhost -U SA Password: 1> create login [FD3S01\Serverworld] from windows; 2> go 1> select name from sys.server_principals; 2> go name -------------------------------------------- sa public sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin ##MS_SQLResourceSigningCertificate## ##MS_SQLReplicationSigningCertificate## ##MS_SQLAuthenticatorCertificate## ##MS_PolicySigningCertificate## ##MS_SmoExtendedSigningCertificate## ##MS_PolicyEventProcessingLogin## ##MS_PolicyTsqlExecutionLogin## ##MS_AgentSigningCertificate## BUILTIN\Administrators NT AUTHORITY\SYSTEM NT AUTHORITY\NETWORK SERVICE FD3S01\Serverworld (22 rows affected) |
| [6] | Login to CentOS Host as an AD user who has SQL Server Login right and make sure it's possible to login to SQL Server, too. |
|
# get Kerberos ticket [serverworld@dlp ~]$ kinit Password for serverworld@SRV.WORLD: [serverworld@dlp ~]$ klist
Ticket cache: KEYRING:persistent:1539401000:1539401000
Default principal: serverworld@SRV.WORLD
Valid starting Expires Service principal
10/12/2017 11:15:37 10/12/2017 21:15:37 krbtgt/SRV.WORLD@SRV.WORLD
renew until 10/19/2017 11:15:27
[serverworld@dlp ~]$ sqlcmd -S dlp.srv.world 1> select @@version; 2> go ----------------------------------------------------------------------- Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core)) (1 rows affected) |
| It's also possible to login to SQL Server on Linux with [Windows Authentication] from Windows Clients that is in Active Directory Domain. |
|
|